package dao.impl;

import dao.BaseDao;
import dao.DormscoresDao;
import entity.Dorm_scores;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DormscoresDaoImpl extends BaseDao implements DormscoresDao {

    @Override
    public List<Dorm_scores> cx3(Integer lid) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;
        List<Dorm_scores> dormScores = new ArrayList<>();

        try {

            StringBuilder sql = new StringBuilder(
                    "SELECT b.score_id, b.check_date, b.hygiene_score, b.discipline_score, " +
                            "b.inspector, a.room_number FROM dorm_rooms a, dorm_scores b " +
                            "WHERE b.room_id = a.room_id");

            if (lid != null && lid > 0) {
                sql.append(" AND b.score_id = ?");
            }

            pst = conn.prepareStatement(sql.toString());

            if (lid != null && lid > 0) {
                pst.setInt(1, lid);
            }

            rs = pst.executeQuery();

            while (rs.next()) {
                Dorm_scores score = new Dorm_scores();
                score.setScore_id(rs.getInt("score_id"));
                score.setCheck_date(rs.getString("check_date"));
                score.setHygiene_score(rs.getInt("hygiene_score"));
                score.setDiscipline_score(rs.getInt("discipline_score"));
                score.setInspector(rs.getString("inspector"));
                score.setRoom_number(rs.getString("room_number"));
                dormScores.add(score);
            }
            return dormScores;
        } finally {
            if (rs != null) rs.close();
            if (pst != null) pst.close();
            if (conn != null) conn.close();
        }
    }

    @Override
    public List<Dorm_scores> cxj(Integer lid, int page, int pageSize) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;
        List<Dorm_scores> dormScores = new ArrayList<>();

        try {

            StringBuilder sql = new StringBuilder(
                    "SELECT b.score_id, b.check_date, b.hygiene_score, b.discipline_score, " +
                            "b.inspector, a.room_number FROM dorm_rooms a, dorm_scores b " +
                            "WHERE b.room_id = a.room_id");

            if (lid != null && lid > 0) {
                sql.append(" AND b.score_id = ?");
            }
            sql.append(" LIMIT ?, ?");

            pst = conn.prepareStatement(sql.toString());
            int paramIndex = 1;

            if (lid != null && lid > 0) {
                pst.setInt(paramIndex++, lid);
            }

            int startPage=page*pageSize;
            pst.setInt(paramIndex++, startPage);
            pst.setInt(paramIndex, pageSize);

            rs = pst.executeQuery();

            while (rs.next()) {
                Dorm_scores score = new Dorm_scores();
                score.setScore_id(rs.getInt("score_id"));
                score.setRoom_number(rs.getString("room_number"));
                score.setCheck_date(rs.getString("check_date"));
                score.setHygiene_score(rs.getInt("hygiene_score"));
                score.setDiscipline_score(rs.getInt("discipline_score"));
                score.setInspector(rs.getString("inspector"));
                dormScores.add(score);
            }
            return dormScores;
        } finally {
            if (rs != null) rs.close();
            if (pst != null) pst.close();
            if (conn != null) conn.close();
        }
    }

    @Override
    public int scj(int id) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement pst = null;

        try {

            String sql = "DELETE FROM dorm_scores WHERE score_id = ?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, id);
            return pst.executeUpdate();
        } finally {
            if (pst != null) pst.close();
            if (conn != null) conn.close();
        }
    }

    @Override
    public int xgj(int score_id, int room_id, String check_date, int hygiene_score,
                   int discipline_score, String inspector) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement pst = null;

        try {

            String sql = "UPDATE dorm_scores SET room_id = ?, check_date = ?, hygiene_score = ?, " +
                    "discipline_score = ?, inspector = ? WHERE score_id = ?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, room_id);
            pst.setString(2, check_date);
            pst.setInt(3, hygiene_score);
            pst.setInt(4, discipline_score);
            pst.setString(5, inspector);
            pst.setInt(6, score_id);
            return pst.executeUpdate();
        } finally {
            if (pst != null) pst.close();
            if (conn != null) conn.close();
        }
    }

    @Override
    public int tjj(int room_id, String check_date, int hygiene_score,
                   int discipline_score, String inspector) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement pst = null;

        try {

            String sql = "INSERT INTO dorm_scores (room_id, check_date, hygiene_score, " +
                    "discipline_score, inspector) VALUES (?, ?, ?, ?, ?)";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, room_id);
            pst.setString(2, check_date);
            pst.setInt(3, hygiene_score);
            pst.setInt(4, discipline_score);
            pst.setString(5, inspector);
            return pst.executeUpdate();
        } finally {
            if (pst != null) pst.close();
            if (conn != null) conn.close();
        }
    }

    @Override
    public int cx1(String room_number) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;

        try {

            String sql = "SELECT room_id FROM dorm_rooms WHERE room_number = ?";
            pst = conn.prepareStatement(sql);
            pst.setString(1, room_number);
            rs = pst.executeQuery();
            return rs.next() ? rs.getInt(1) : 0;
        } finally {
            if (rs != null) rs.close();
            if (pst != null) pst.close();
            if (conn != null) conn.close();
        }
    }

    @Override
    public int getTotalCount(Integer lid) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;

        try {

            StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM dorm_scores WHERE 1=1");

            if (lid != null && lid > 0) {
                sql.append(" AND score_id = ?");
            }

            pst = conn.prepareStatement(sql.toString());

            if (lid != null && lid > 0) {
                pst.setInt(1, lid);
            }

            rs = pst.executeQuery();
            return rs.next() ? rs.getInt(1) : 0;
        } finally {
            if (rs != null) rs.close();
            if (pst != null) pst.close();
            if (conn != null) conn.close();
        }
    }
}